[筆記,MySQL]計算陣列中不重複數量

mysql function 陣列不重複數量

舜~ 2019-11-01 15:53:57 ‧ 2265 瀏覽

分享至

因為遇到這篇問題，為了解決問題而撰寫此CountArrayDistinct方法。
關於本方法有任何更好的做法、可以提升效能或是任何建議，歡迎提出討論~~

-- ========================
-- CountArrayDistinct
-- 建立方法，計算陣列文字(ex."a,b,c,b")中不重複的數量
-- ArrStr VARCHAR(21845) 要進行處理的字串
-- pos VARCHAR(10) 分割字串
-- ===================

DELIMITER $$
DROP FUNCTION IF EXISTS CountArrayDistinct $$
CREATE FUNCTION CountArrayDistinct(ArrStr VARCHAR(21845),pos VARCHAR(10)) RETURNS int
BEGIN

SET @startIndex = 0;
SET @posIndex = 0;
set @result = 0;
-- 如果已經有臨時表了就不用重建
CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_CountArrayDistinct_table`
(
    name VARCHAR(1000) NOT NULL
);
-- 清空臨時表避免上一次的殘留
delete from tmp_CountArrayDistinct_table;
  
SET @posIndex = LOCATE(pos,ArrStr);
if @posIndex > 0 then
    -- 依序加分割的字串項目加入臨時表(最後一個會被略過)
    while  @posIndex > 0 do
		-- 加入
		insert into tmp_CountArrayDistinct_table values(SUBSTRING(ArrStr,@startIndex+1,@posIndex-@startIndex-1));
		-- 處理下一個起始索引
		set @startIndex = @posIndex;
		-- 找下一個
		SET @posIndex = LOCATE(pos,ArrStr,@startIndex+1);
    end while;

    -- 將最後一個字串項目加入臨時表
    insert into tmp_CountArrayDistinct_table values(SUBSTRING_INDEX(ArrStr,pos,-1));
    -- 輸出不重複數量
    set @result = (select count(DISTINCT name ) from tmp_CountArrayDistinct_table where name!='' and name is not null);

ELSEIf  LENGTH(ArrStr)>0 THEN
    -- 有傳入文字，但沒找到分割字元
	set @result = 1;
else
    set @result = 0;
END IF;

RETURN @result;

END $$
DELIMITER ;

感恩ckp6250大大提供優化版本

SELECT
	count(1) 
FROM
	(
	SELECT
		@num := @num + 1,
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', @num ), ',',- 1 ) AS str 
	FROM
		information_schema.GLOBAL_STATUS a,
		( SELECT @num := 0 ) b 
	WHERE
		@num < CHAR_LENGTH( 'a,b,c,b,a,z' )- CHAR_LENGTH(REPLACE ( 'a,b,c,b,a,z', ',', '' ))+ 1
	GROUP BY str 
	) a

使用範例

select CountArrayDistinct( 'a,b,c,b,a,z' , ',' );
-- 輸出 4

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 則留言

ckp6250

iT邦好手 1 級 ‧ 2019-11-01 17:40:53

又要開 temp table, 又要 delete 又要 insert , 您的效能，肯定不好。

我的姿勢醜一點，沒關係吧。

SELECT
	COUNT(*) 
FROM
	(
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 1 ), ',',- 1 ) AS str UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 2 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 3 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 4 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 5 ), ',',- 1 ) UNION
SELECT
	SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 6 ), ',',- 1 )) a

原理如此，細節您自行變化。

回應 4
檢舉

看更多先前的回應...收起先前的回應...

一級屠豬士 iT邦大師 1 級 ‧ 2019-11-01 17:50:23 檢舉

可是長度不知道,那你這個 union 幾個,要怎樣決定?

ckp6250 iT邦好手 1 級 ‧ 2019-11-01 19:16:08 檢舉

我是提醒原理啦，樓主若是領略其義，就能自行變化。

不必用到 temp table, delete ，　insert　等等傷筋動骨的東東。

其實，任何應用環境都有其【可能限度】，若是我，真有可能這麼幹，總比寫一堆迴圈或函數變數要方便多了。

黑貓白貓論。

若嫌姿勢醜，那就改成迴圈和變數吧，至少可以達成樓主說的【提升效能】之要求。

舜~ iT邦高手 1 級 ‧ 2019-11-01 20:55:23 檢舉

抱歉小弟資質愚昧，ckp6250大大能再詳細指點嗎?
大大的意思是算出多少個分隔字串後(數量 = (原始長度-空字串取代後的長度)/分隔符的長度)，動態組合出UNION起來的sql再執行??

ckp6250 iT邦好手 1 級 ‧ 2019-11-01 21:12:55 檢舉

動態組合出UNION起來的sql再執行
是可行方法，

　　又或者，算出有幾個字串，寫個迴圈，個別取出字串，判斷有無重覆，沒有重覆的，就用變數累加１。

反正，您要的這個東西，根本不需要用到 table , insert , delete 等等耗能工具。

登入發表回應

ckp6250

iT邦好手 1 級 ‧ 2019-11-01 21:33:40

一指搞定。

SELECT
	count(*) 
FROM
	(
	SELECT
		@num := @num + 1,
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', @num ), ',',- 1 ) AS str 
	FROM
		information_schema.GLOBAL_STATUS a,
		( SELECT @num := 0 ) b 
	WHERE
		@num < CHAR_LENGTH( 'a,b,c,b,a,z' )- CHAR_LENGTH(REPLACE ( 'a,b,c,b,a,z', ',', '' ))+ 1
	GROUP BY
	str 
	) a